package DBLayer;
import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

public class DBSalesLine {

	private Connection con;
	
	// Constructor
	public DBSalesLine(){
		con = DbConnection1.getInstance().getDBcon();
	}
	
	// Get all salesLines
	public ArrayList<SalesLine> getAllSalesLine(boolean retriveAssociation){
		return miscWhere("", retriveAssociation);
	}

	// Find via salesOrderId
	public SalesLine findSalesLine(String salesOrderId, boolean retriveAssociation){
		String wClause = "salesOrderId = '" + salesOrderId + "'";
		return singleWhere(wClause, retriveAssociation);
	}
	
	// Search for productId
	public SalesLine searchProdId(String prodId, boolean retriveAssociation){
		String wClause = "prodId = '%" + prodId + "%'";
		System.out.println("SearchProdId" + wClause);
		return singleWhere(wClause, retriveAssociation);
	}
	
	// Insert new salesLine
	
	public int insertSalesLine(SalesLine salesline) throws Exception{
		int nextSalesLine = GetMax.getMaxId("Select max(salesOrderId) from salesLine");
		nextSalesLine = nextSalesLine++;
		System.out.println("next salesline =" + nextSalesLine);
		
		int rc = -1;
		String query="INSERT INTO salesLine(salesOrderId, prodId, quantity) Values('" +
		nextSalesLine + "','" + 
		salesline.getProdId() + "'," +
		salesline.getQuantity() + ")";

		// Might be missing calls for the last attributes that can be null
		
		System.out.println("insert" + query);
		
		try{ // insert new employee +  dependent
	          Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  rc = stmt.executeUpdate(query);
	          stmt.close();
	      }//end try
	       catch(SQLException ex){
	          System.out.println("SalesLine not created");
	          throw new Exception ("SalesLine is not inserted correct");
	       }
	       return(rc);
	}
	
	// to be updated
	
	public int updateSalesLine(SalesLine sales){
		SalesLine salesObj = sales;
		
		int rc = -1;
		String query = "UPDATE product SET "+
				"salesOrderId = '" + salesObj.getSalesOrderId() + "', " +
				"prodId = '" + salesObj.getProdId() + "', "+
				"quantity ='" + salesObj.getQuantity() + "'";
				System.out.println("Update query:" + query);
				
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}
		catch(Exception ex){
	 	 	System.out.println("Update exception in product db: "+ex);
	  	}
		return(rc);
	}
		
	public int delete(String salesOrderId){

		int rc = -1;
		
		String query="DELETE FROM SalesLines WHERE salesOrderId = '" +
				salesOrderId + "'";
                System.out.println(query);
	  	try{ // delete from employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//end try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in SalesLines db: "+ex);
   	        }
		return(rc);
	}		
	
	private SalesLine singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		SalesLine sLiObj = new SalesLine();
                
        String query =  buildQuery(wClause);
        System.out.println(query);
        
		try{ // read the employee from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
	 			sLiObj = buildSalesLine(results);
                //association is to be build
                stmt.close();
                
                /*if(retrieveAssociation)
                {   //The supervisor and department is to be build as well
                    DBSalesOrder dbsalesObj = new DBSalesOrder();
                    custObj.setSalesOrderList(dbsalesObj.findCostumerSalesOrder(custObj.getCustomerId(), false));                   
                }*/
			}
            else{ //no invoice was found
            	sLiObj = null;
            }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return sLiObj;
	}

	private ArrayList<SalesLine> miscWhere(String wClause, boolean retrieveAssociation)
	{
        ResultSet results;
	    ArrayList<SalesLine> list = new ArrayList<SalesLine>();	
		
	    String query =  buildQuery(wClause);
  
        try{ // read the invoice from the database
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);
	 	
	
			while( results.next() ){
				SalesLine sLiObj = new SalesLine();
		     	sLiObj = buildSalesLine(results);	
		     	list.add(sLiObj);	
			}//end while
			
			stmt.close();  
			
			/*if(retrieveAssociation)
		    {   //The supervisor and department is to be build as well
				for(Employee empObj : list){
		            String superssn = empObj.getSupervisor().getSsn();
		            Employee superEmp = singleWhere(" ssn = '" + superssn + "'",false);
		            empObj.setSupervisor(superEmp);
		            System.out.println("Supervisor is selected");
		           // here the department has to be selected as well
		        }
		    }//end if  */ 
				
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
        
		return list;
	}
	
	
	
	// method for query methods
	private String buildQuery(String wClause)
	{
	    String query="SELECT salesOrderId, prodId, quantity FROM customer";		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	
	// create a customer for the table
	private SalesLine buildSalesLine(ResultSet results)
		{
			SalesLine salesLineObj = new SalesLine();
	        try{ // the columns from the table costumer are used
	        	salesLineObj.setSalesOrderId(results.getString("salesOrderId"));
	        	salesLineObj.setProdId(results.getString("prodId"));
	        	salesLineObj.setQuantity(results.getInt("quantity"));

	        }
	        catch(Exception e)
	        {
	           System.out.println("Error in building the SalesLine object");
	        }
	        return salesLineObj;
	    }
}